﻿Imports MySql.Data

Public Class RegionalReports

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        For x = 0 To DataGridView1.Rows.Count - 1
            DataGridView1.Rows.RemoveAt(0)
        Next

        Dim Var As New Variables


        Dim DataTable As New DataTable
        Dim dm As New DataTable

        Try
            Var.Conn.Open()

            Var.Comm = New MySqlClient.MySqlCommand("select * from tbl_Accounts where acc_num = '" & TextBox1.Text & "'", Var.Conn)
            Var.Adapter = New MySqlClient.MySqlDataAdapter(Var.Comm)
            Var.Adapter.Fill(DataTable)

            Label1.Text = DataTable.Rows(0)("acc_name")
            Label2.Text = DataTable.Rows(0)("acc_num")
            Label3.Text = DataTable.Rows(0)("acc_type")

            Var.Comm = New MySqlClient.MySqlCommand("select * from tbl_Accounts where acc_id_up = '" & DataTable.Rows(0)("acc_id") & "'", Var.Conn)
            Var.Adapter = New MySqlClient.MySqlDataAdapter(Var.Comm)
            Var.Adapter.Fill(dm)

            Var.Conn.Close()
        Catch ex As Exception

        End Try



        If dm.Rows.Count > 0 Then
            For x = 0 To dm.Rows.Count - 1
                Dim dmSales As New DataTable

                Dim connString As String = "SELECT SUM(dbo.tbl_Sales.sales_amount) AS sales_amount FROM dbo.tbl_Accounts INNER JOIN dbo.tbl_Sales ON dbo.tbl_Accounts.acc_id = dbo.tbl_Sales.acc_id where acc_id_up = '" & dm.Rows(x)("acc_id") & "' "

                Try
                    Var.Conn.Open()

                    Var.Comm = New MySqlClient.MySqlCommand(connString, Var.Conn)
                    Var.Adapter = New MySqlClient.MySqlDataAdapter(Var.Comm)
                    Var.Adapter.Fill(dmSales)

                    Var.Conn.Close()
                Catch ex As Exception

                End Try

                Dim amount As String = ""

                If dmSales.Rows(0)("sales_amount").ToString = "" Then
                    amount = "0"
                Else
                    amount = dmSales(0)("sales_amount")
                End If

                DataGridView1.Rows.Add(dm.Rows(x)("acc_id"), dm.Rows(x)("acc_num"), dm.Rows(x)("acc_name"), amount)

            Next



            Dim total As Integer = 0

            For x = 0 To DataGridView1.Rows.Count - 1
                total += DataGridView1.Rows(x).Cells("sales_amount").Value
            Next

            RadLabel7.Text = total.ToString()

        End If




    End Sub

    Private Sub RadButton1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles RadButton1.Click
        For x = 0 To DataGridView1.Rows.Count - 1
            DataGridView1.Rows.RemoveAt(0)
        Next

        Dim Var As New Variables


        Dim DataTable As New DataTable
        Dim dm As New DataTable

        Try
            Var.Conn.Open()

            Var.Comm = New MySqlClient.MySqlCommand("select * from tbl_Accounts where acc_num = '" & TextBox1.Text & "'", Var.Conn)
            Var.Adapter = New MySqlClient.MySqlDataAdapter(Var.Comm)
            Var.Adapter.Fill(DataTable)

            Label1.Text = DataTable.Rows(0)("acc_name")
            Label2.Text = DataTable.Rows(0)("acc_num")
            Label3.Text = DataTable.Rows(0)("acc_type")

            Var.Comm = New MySqlClient.MySqlCommand("select * from tbl_Accounts where acc_id_up = '" & DataTable.Rows(0)("acc_id") & "'", Var.Conn)
            Var.Adapter = New MySqlClient.MySqlDataAdapter(Var.Comm)
            Var.Adapter.Fill(dm)

            Var.Conn.Close()
        Catch ex As Exception

        End Try



        If dm.Rows.Count > 0 Then
            For x = 0 To dm.Rows.Count - 1
                Dim dmSales As New DataTable

                Dim connString As String = "SELECT SUM(dbo.tbl_Sales.sales_amount) AS sales_amount FROM dbo.tbl_Accounts INNER JOIN dbo.tbl_Sales ON dbo.tbl_Accounts.acc_id = dbo.tbl_Sales.acc_id where acc_id_up = '" & dm.Rows(x)("acc_id") & "' and dbo.tbl_Sales.sales_date between '" & RadDateTimePicker1.Value & "' and '" & RadDateTimePicker2.Value & "'"

                Try
                    Var.Conn.Open()

                    Var.Comm = New MySqlClient.MySqlCommand(connString, Var.Conn)
                    Var.Adapter = New MySqlClient.MySqlDataAdapter(Var.Comm)
                    Var.Adapter.Fill(dmSales)

                    Var.Conn.Close()
                Catch ex As Exception

                End Try

                Dim amount As String = ""

                If dmSales.Rows(0)("sales_amount").ToString = "" Then
                    amount = "0"
                Else
                    amount = dmSales(0)("sales_amount")
                End If

                DataGridView1.Rows.Add(dm.Rows(x)("acc_id"), dm.Rows(x)("acc_num"), dm.Rows(x)("acc_name"), amount)

            Next



            Dim total As Integer = 0

            For x = 0 To DataGridView1.Rows.Count - 1
                total += DataGridView1.Rows(x).Cells("sales_amount").Value
            Next

            RadLabel7.Text = total.ToString()

        End If



    End Sub
End Class
